<?php
/**
 * Created by Zhy
 * User: Zhy
 * Date: 2020/2/13
 * Time: 14:39
 */

namespace app\admin\controller;


use think\Db;
use think\Request;

class Dyn extends Base
{
    /**
     * 首页
     */
    public function index(){
        if(request()->isAjax()){
            extract(input());
            $where = [
                's_admin' => ['<>',3]
            ];
            if(isset($user_name) && $user_name != ''){
                $where['s_u_name|s_u_phone'] = ['like', '%' . $user_name . '%'];
            }
            if(isset($user_name1) && $user_name1 != ''){
                $where['s_com_name|s_name|s_phone|s_phone1|s_phone2'] = ['like', '%' . $user_name1 . '%'];
            }
            if(isset($user_name2) && $user_name2 != ''){
                $where['s_inside_name|s_inside_phone'] = ['like', '%' . $user_name2 . '%'];
            }
            if(isset($s_verify) && $s_verify != ''){
                $where['s_verify'] = $s_verify;
            }
            if(isset($s_status) && $s_status != ''){
                $where['s_status'] = $s_status;
                if($s_status == 3){
                    $where['s_status'] = ['>=',3];
                }
            }
            if(isset($province) && $province != ''){
                $where['s_sheng'] = $province;
            }
            if(isset($city) && $city != ''){
                $where['s_shi'] = $city;
            }
            if(isset($district) && $district != ''){
                $where['s_qu'] = $district;
            }
            if(isset($address) && $address != ''){
                $where['s_address|s_area_str'] = ['like', '%' . $address . '%'];
            }
            if(isset($s_admin) && $s_admin != ''){
                $where['s_admin'] = $s_admin;
            }
            if(isset($s_protect) && $s_protect != ''){
                $where['s_protect'] = $s_protect;
            }
            if(isset($s_excelin) && $s_excelin != ''){
                $where['s_excelin'] = $s_excelin;
            }
            $NowPage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $count = Db::table('up_shop')
                ->where($where)
                ->count();// 获取总条数
            $lists = Db::table('up_shop')
                ->where($where)
                ->page($NowPage,$limits)
                ->order('s_status asc,s_verify asc,s_update_time desc')
                ->select();
            if($lists){
                foreach ($lists as $k => $v){
                    $lists[$k]['red_son'] = 0;
                    $have = Db::table('up_shop_image')
                        ->where('si_s_id',$v['s_id'])
                        ->where('si_status',1)
                        ->value('si_id');
                    if($have){
                        $lists[$k]['red_son'] = 1;
                    }
                }
            }
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        $province = Db::table('up_area')->where('level', 1)->select();
        $this->assign([
            'province' => $province,
        ]);
        return $this->fetch();
    }
    /**
     * 添加
     * @return mixed
     */
    public function add(){
        if(Request::instance()->isPost()){
            extract(input());
            $today = date('Y-m-d H:i:s');
            $lalo = explode(',',$lonlat);
            $a1 = Db::table('up_area')->where('district_id', $s_sheng)->value('district');
            $a2 = Db::table('up_area')->where('district_id', $s_shi)->value('district');
            $a3 = Db::table('up_area')->where('district_id', $s_qu)->value('district');
            $update = [
                's_u_name' => $s_u_name,
                's_u_phone' => $s_u_phone,
                's_u_userid' => 0,
                's_com_name' => $s_com_name,
                's_name' => $s_name,
                's_phone' => $s_phone,
                's_phone1' => $s_phone1,
                's_phone2' => $s_phone2,
                's_inside_name' => $s_inside_name,
                's_inside_phone' => $s_inside_phone,
                's_verify' => 2,
                's_status' => 2,
                's_time' => $today,
                's_endtime' => $today,
                's_update_time' => $today,
                's_address' => $s_address,
                's_sheng' => $s_sheng,
                's_shi' => $s_shi,
                's_qu' => $s_qu,
                's_area_str' => $a1 . ' ' . $a2 . ' ' . $a3,
                's_lon' => $lalo[1],
                's_lat' => $lalo[0],
                's_foradmin_time' => $today,
                's_admin' => 2,
                's_year_other' => $s_year_other,
                's_time_other' => $s_time_other,
                's_done_other' => $s_done_other,
            ];
            $res = Db::table('up_shop')->insert($update);
            if($res){
                return json(['code' => 200, 'msg' => '成功']);
            }else{
                return json(['code' => 100, 'msg' => '失败']);
            }
        }
        $province = Db::table('up_area')->where('level', 1)->select();
        $this->assign([
            'province' => $province,
        ]);
        return $this->fetch();
    }

    /**
     * 审核
     * @return mixed
     */
    public function shenhe(){
        if(Request::instance()->isPost()){
            $s_id = input('s_id');
            $si_id = input('si_id');
            $verify = input('verify');
            $reason = trim(input('reason'), ' ');
            $shop = Db::table('up_shop')->where('s_id', $s_id)->find();
            if($shop['s_verify'] != 1){
                return json(['code' => 100, 'msg' => '只有待审核的报备才可以审核']);
            }

            if($verify == 2){
                $config = Db::table('up_shop_config')->where('id', 1)->find();
                $lalo = Db::table('up_shop')->where('s_id', $s_id)->field('s_lon,s_lat')->find();
                $lat = $lalo['s_lat'];
                $lon = $lalo['s_lon'];
                $mile = $config['miles'];
                $miles = (int)$mile;
                $field = 's_id, s_com_name';
                $field .= ',ROUND(6378.138 * 2 * ASIN(SQRT(POW(SIN(('.$lat.'* PI() / 180 - s_lat * PI() / 180) / 2),2) + COS('.$lat.' * PI() / 180) * COS(s_lat * PI() / 180) * POW(SIN(('.$lon.' * PI() / 180 - s_lon * PI() / 180) / 2),2))) * 1000) AS distance';
                $alredyJoin = Db::table('up_shop')
                    ->where('s_id', '<>', $s_id)
                    ->where(['s_status' => 2, 's_protect' => 1])
                    ->having("distance <= $miles")
                    ->field($field)
                    ->find();
                if($alredyJoin) return json(['code' => 100, 'msg' => '保护范围内已经有加盟的商家了！']);
            }
            $update1 = [
                's_verify' => $verify,
                's_reason' => $reason,
                's_foradmin_time' => date('Y-m-d H:i:s'),
            ];
            $update2 = [
                'si_status' => $verify,
                'si_update_time' => date('Y-m-d H:i:s')
            ];
            Db::startTrans();
            try{
                Db::table('up_shop')->where('s_id', $s_id)->update($update1);
                Db::table('up_shop_image')->where('si_id', $si_id)->update($update2);
                Db::commit();
                return json(['code' => 200, 'msg' => '成功']);
            }catch (\Exception $e) {
                Db::rollback();
                return json(['code' => 100, 'msg' => '失败']);
            }
        }
        $s_id = input('s_id');
        $data = Db::table('up_shop')
            ->where('s_id', $s_id)
            ->find();
        $detail = Db::table('up_shop_image')
            ->where('si_s_id', $s_id)
            ->order('si_time desc')
            ->find();
        if($detail['si_images']){
            $img = trim($detail['si_images'], ',');
            $detail['si_images'] = explode(',', $img);
        }
        $this->assign('data', $data);
        $this->assign('detail', $detail);
        return $this->fetch();
    }

    /**
     * 周边店铺
     * @return mixed|\think\response\Json
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function rimShop(){
        if(request()->isAjax()){
            $where = [
                's_admin' => ['<>',3],
                's_protect' => 1,
                's_id' => ['<>', session('sId')]
            ];
            $config = Db::table('up_shop_config')->where('id', 1)->find();
            $lalo = Db::table('up_shop')->where('s_id', session('sId'))->field('s_lon, s_lat')->find();
            $lat = $lalo['s_lat'];
            $lon = $lalo['s_lon'];
            $mile = $config['miles'];
            $miles = (int)$mile;
            $field = 's_id, s_com_name, s_u_phone, s_u_name, s_name, s_area_str, s_address, s_status, s_lat, s_lon, 
            ROUND(6378.138 * 2 * ASIN(SQRT(POW(SIN((' . $lat . '* PI() / 180 - s_lat * PI() / 180) / 2),2) + COS(' . $lat . ' * PI() / 180) * COS(s_lat * PI() / 180) * POW(SIN((' . $lon . ' * PI() / 180 - s_lon * PI() / 180) / 2),2))) * 1000) AS distance';
            $NowPage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
//            $count1 = Db::table('up_shop')
//                ->where($where)
//                ->having("distance <= $miles")
//                ->field($field)
//                ->group('s_id')
//                ->fetchSql(true)
//                ->select();// 获取总条数
//            $count = count($count1);
//            $buildSql = Db::table('up_shop')
//                ->where($where)
//                ->having("distance <= $miles")
//                ->field($field)
//                ->group('s_id')
//                ->buildSql();
//            $lists = Db::table($buildSql)
//                ->alias('a')
//                ->field($field)
//                ->page($NowPage, $limits)
//                ->select();
            try{
                $lists = Db::table('up_shop')
                    ->where($where)
                    ->having("distance <= $miles")
                    ->field($field)
//                    ->page($NowPage, $limits)
                    ->group('s_id')
                    ->select();
                $count = count($lists);
                return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
            }catch (\Exception $e){
                return json(['code' => 100, 'msg' => $e->getLine() . ':' . $e->getMessage(), 'count' => 0, 'data' => []]);
            }


        }
        $s_id = input('s_id');
        if($s_id){
            session('sId', $s_id);
        }
        return $this->fetch();
    }

    /**
     * 编辑
     * @return mixed
     */
    public function update(){
        if(Request::instance()->isPost()){
            $s_id = input('s_id');
            $s_com_name = input('s_com_name');
            $s_name = input('s_name');
            $s_inside_name = trim(input('s_inside_name'), ' ');
            $s_inside_phone = input('s_inside_phone');
            $s_year_other = input('s_year_other');
            $s_time_other = input('s_time_other');
            $s_done_other = input('s_done_other');
            $s_protect = input('s_protect');
            $update = [
                's_com_name' => $s_com_name,
                's_name' => $s_name,
                's_inside_name' => $s_inside_name,
                's_inside_phone' => $s_inside_phone,
                's_year_other' => $s_year_other,
                's_time_other' => $s_time_other,
                's_done_other' => $s_done_other,
                's_foradmin_time' => date('Y-m-d H:i:s')
            ];
            $data = Db::table('up_shop')->where('s_id', $s_id)->field('s_protect,s_status')->find();
            if($data['s_status'] == 2){
                $update['s_protect'] = $s_protect;
            }
            if($data['s_protect'] != $s_protect && $s_protect == 1 && $data['s_status'] == 2){
//----------------------------------------区域保护 start -------------------------------------------------
                $config = Db::table('up_shop_config')->where('id',1)->find();
                $lalo = Db::table('up_shop')->where('s_id', $s_id)->field('s_lon,s_lat')->find();
                $lat = $lalo['s_lat'];
                $lon = $lalo['s_lon'];
                $mile = $config['miles'];
                $miles = (int)$mile;
                $field = 's_id,s_com_name';
                $field .= ',ROUND(6378.138 * 2 * ASIN(SQRT(POW(SIN(('.$lat.'* PI() / 180 - s_lat * PI() / 180) / 2),2) + COS('.$lat.' * PI() / 180) * COS(s_lat * PI() / 180) * POW(SIN(('.$lon.' * PI() / 180 - s_lon * PI() / 180) / 2),2))) * 1000) AS distance';
                $alredy_join = Db::table('up_shop')
                    ->where('s_id','<>',$s_id)
                    ->where('s_status',2)
                    ->having("distance <= $miles")
                    ->field($field)
                    ->select();
                if($alredy_join){
                    $arr2 = array_column($alredy_join, 's_id');
                    Db::table('up_shop')->where('s_id','in',$arr2)->update([
                        's_verify' => 3,
                        's_status' => 1,
                        's_reason' => '保护区域内已经有加盟客户了',
                        's_foradmin_time' => date('Y-m-d H:i:s')
                    ]);
                    $have2 = Db::table('up_shop_image')
                        ->where('si_s_id','in',$arr2)
                        ->where('si_status',1)
                        ->find();
                    if($have2){
                        Db::table('up_shop_image')
                            ->where('si_s_id','in',$arr2)
                            ->where('si_status',1)
                            ->update([
                                'si_status' => 3 ,
                                'si_update_time' => date('Y-m-d H:i:s')
                            ]);
                    }
                }
                //----------------------------------------区域保护  end --------------------------------------------------
            }
            $res = Db::table('up_shop')->where('s_id', $s_id)->update($update);
            if($res){
                return json(['code' => 200, 'msg' => '成功']);
            }else{
                return json(['code' => 100, 'msg' => '失败']);
            }
        }
        $s_id = input('s_id');
        $data = Db::table('up_shop')
            ->where('s_id', $s_id)
            ->find();
        $this->assign('data', $data);
        return $this->fetch();
    }

    /**
     * 位置校正
     * @return mixed
     */
    public function updateLocation(){
        if(Request::instance()->isPost()){
            extract(input());
            $today = date('Y-m-d H:i:s');
            $lalo = explode(',',$lonlat);
            $a1 = Db::table('up_area')->where('district_id', $s_sheng)->value('district');
            $a2 = Db::table('up_area')->where('district_id', $s_shi)->value('district');
            $a3 = Db::table('up_area')->where('district_id', $s_qu)->value('district');
            $update = [
                's_com_name' => $s_com_name,
                's_name' => $s_name,
                's_phone' => $s_phone,
                's_inside_name' => $s_inside_name,
                's_inside_phone' => $s_inside_phone,
                's_sheng' => $s_sheng,
                's_shi' => $s_shi,
                's_qu' => $s_qu,
                's_address' => $s_address,
                's_area_str' => $a1 . ' ' . $a2 . ' ' . $a3,
                's_lon' => $lalo[1],
                's_lat' => $lalo[0],
                's_foradmin_time' => $today
            ];
            $res = Db::table('up_shop')->where('s_id', $s_id)->update($update);
            if($res){
                return json(['code' => 200, 'msg' => '成功']);
            }else{
                return json(['code' => 100, 'msg' => '失败']);
            }
        }
        $s_id = input('s_id');
        $data = Db::table('up_shop')
            ->where('s_id', $s_id)
            ->find();
        $province = Db::table('up_area')->where('level', 1)->field('district_id, district')->select();
//        if(!$data['s_sheng']){
//            $data['s_sheng'] = $province[0]['district_id'];
//        }
        $city = Db::table('up_area')->where('pid', $data['s_sheng'])->field('district_id, district')->select();
//        if(!$data['s_shi']){
//            $data['s_shi'] = $city[0]['district_id'];
//        }
        $district = Db::table('up_area')->where('pid', $data['s_shi'])->field('district_id, district')->select();
//        if(!$data['s_qu']){
//            $data['s_qu'] = $district[0]['district_id'];
//        }

        $this->assign('data', $data);
        $this->assign('province', $province);
        $this->assign('city', $city);
        $this->assign('district', $district);
        return $this->fetch('dyn/update2');
    }

    /**
     * 取消加盟资格
     * @return \think\response\Json
     */
    public function cancelAuth(){
        $id = input('id');
        $res = Db::table('up_shop')->where('s_id', $id)->update([
            's_status' => 4 ,
            's_foradmin_time' => date('Y-m-d H:i:s')
        ]);
        if($res){
            return json(['code' => 200, 'msg' => '成功']);
        }else{
            return json(['code' => 100, 'msg' => '失败']);
        }
    }

    /**
     * 确定加盟吗
     * @return \think\response\Json
     */
    public function sureJoin(){
        $id = input('id');
        Db::startTrans();
        try{
            Db::table('up_shop')->where('s_id', $id)->update([
                's_status' => 2 ,
                's_foradmin_time' => date('Y-m-d H:i:s')
            ]);
            $have = Db::table('up_shop_image')
                ->where('si_s_id',$id)
                ->where('si_status',1)
                ->find();
            if($have){
                Db::table('up_shop_image')
                    ->where('si_s_id',$id)
                    ->where('si_status',1)
                    ->update([
                        'si_status' => 2 ,
                        'si_update_time' => date('Y-m-d H:i:s')
                    ]);
            }
            //----------------------------------------区域保护 start -------------------------------------------------
            $config = Db::table('up_shop_config')->where('id',1)->find();
            $lalo = Db::table('up_shop')->where('s_id', $id)->field('s_lon,s_lat')->find();
            $lat = $lalo['s_lat'];
            $lon = $lalo['s_lon'];
            $mile = $config['miles'];
            $miles = (int)$mile;
            $field = 's_id,s_com_name';
            $field .= ',ROUND(6378.138 * 2 * ASIN(SQRT(POW(SIN(('.$lat.'* PI() / 180 - s_lat * PI() / 180) / 2),2) + COS('.$lat.' * PI() / 180) * COS(s_lat * PI() / 180) * POW(SIN(('.$lon.' * PI() / 180 - s_lon * PI() / 180) / 2),2))) * 1000) AS distance';
            $alredy_join = Db::table('up_shop')
                ->where('s_id','<>',$id)
                ->where('s_status',1)
                ->where('s_verify',2)
                ->where('s_protect',1)
                ->having("distance <= $miles")
                ->field($field)
                ->select();
            if($alredy_join){
                $arr2 = array_column($alredy_join, 's_id');
                Db::table('up_shop')->where('s_id','in',$arr2)->update([
                    's_verify' => 3,
                    's_status' => 1,
                    's_reason' => '保护区域内已经有加盟客户了',
                    's_foradmin_time' => date('Y-m-d H:i:s')
                ]);
                $have2 = Db::table('up_shop_image')
                    ->where('si_s_id','in',$arr2)
                    ->where('si_status',1)
                    ->find();
                if($have2){
                    Db::table('up_shop_image')
                        ->where('si_s_id','in',$arr2)
                        ->where('si_status',1)
                        ->update([
                            'si_status' => 3 ,
                            'si_update_time' => date('Y-m-d H:i:s')
                        ]);
                }
            }
            //----------------------------------------区域保护  end --------------------------------------------------

            Db::commit();
            return json(['code' => 200, 'msg' => '成功']);
        }catch (\Exception $e) {
            Db::rollback();
            return json(['code' => 100, 'msg' => '失败']);
        }
    }

    /**
     * 删除帖子
     * @return \think\response\Json
     */
    public function dynDel(){
        $id = input('id');
        Db::startTrans();
        try{
            Db::table('up_shop')->where('s_id', $id)->delete();
            Db::table('up_shop_image')->where('si_s_id', $id)->delete();
            Db::commit();
            return json(['code' => 200, 'msg' => '成功']);
        }catch (\Exception $e) {
            Db::rollback();
            return json(['code' => 100, 'msg' => '失败']);
        }
    }

    /**
     * 跟进记录
     * @return mixed|\think\response\Json
     */
    public function dynComment(){
        if(request()->isAjax()){
            $where['si_s_id'] = session('s_id');
            $NowPage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $count = Db::table('up_shop_image')
                ->where($where)
                ->count();// 获取总条数
            $lists = Db::table('up_shop_image')
                ->where($where)
                ->page($NowPage,$limits)
                ->order('si_status asc,si_time desc')
                ->select();
            if($lists){
                foreach ($lists as $k => $v){
                    if($v['si_images']){
                        $lists[$k]['si_images'] = explode(',',$v['si_images']);
                    }
                }
            }
            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        $s_id = input('s_id');
        session('s_id', $s_id);
        return $this->fetch('dyn/dyncomment');
    }
    
    /**
     * 通过
     * @return \think\response\Json
     */
    public function godyes(){
        $siid = input('id');
        $config = Db::table('up_shop_config')->where('id',1)->find();
        $insert = Db::table('up_shop_image')->where('si_id', $siid)->find();
        $sid = Db::table('up_shop_image')->where('si_id', $siid)->value('si_s_id');
        Db::startTrans();
        try{
            $shop = Db::table('up_shop')->where('s_id',$sid)->field('s_days,s_endtime')->find();
            if($shop['s_days'] < $config['alldays']){
                if($shop['s_days'] + $insert['si_days'] >= $config['alldays']){
                    $chengyi = $config['alldays']-$shop['s_days'];
                    $update = [
                        's_days' => $config['alldays'],
                        's_endtime' => date('Y-m-d H:i:s',strtotime($shop['s_endtime'])+(3600*24*$chengyi)),
                        's_update_time' => date('Y-m-d H:i:s'),
                        's_foradmin_time' => date('Y-m-d H:i:s'),
                    ];
                }else{
                    $chengyi = $insert['si_days'];
                    $update = [
                        's_days' => $shop['s_days'] + $insert['si_days'],
                        's_endtime' => date('Y-m-d H:i:s',strtotime($shop['s_endtime'])+(3600*24*$chengyi)),
                        's_update_time' => date('Y-m-d H:i:s'),
                        's_foradmin_time' => date('Y-m-d H:i:s'),
                    ];
                }
                Db::table('up_shop')->where('s_id',$sid)->update($update);
            }
            Db::table('up_shop_image')->where('si_id', $siid)->update([
                'si_status' => 2,
                'si_update_time' => date('Y-m-d H:i:s')
            ]);
            Db::commit();
            return json(['code' => 200, 'msg' => '操作成功']);
        }catch(\Exception $e){
            Db::rollback();
            return json(['code' => 100, 'msg' => '操作失败']);
        }
    }

    /**
     * 拒绝
     * @return \think\response\Json
     */
    public function godno(){
        $siid = input('id');
        $res = Db::table('up_shop_image')->where('si_id', $siid)->update([
            'si_status' => 3,
            'si_update_time' => date('Y-m-d H:i:s')
        ]);
        if($res){
            return json(['code' => 200, 'msg' => '成功']);
        }else{
            return json(['code' => 100, 'msg' => '失败']);
        }
    }

    /**
     * 删除
     * @return \think\response\Json
     */
    public function commentDel(){
        $siid = input('id');
        $res = Db::table('up_shop_image')->where('si_id', $siid)->delete();
        if($res){
            return json(['code' => 200, 'msg' => '成功']);
        }else{
            return json(['code' => 100, 'msg' => '失败']);
        }
    }

    /**
     * 导出Excel
     * @return \think\response\Json
     */
    public function outExcel(){
        extract(input());
        set_time_limit(0);//防止超时
        //ini_set("memory_limit", "512M");//防止内存溢出
        $where = [
            's_admin' => ['<>',3]
        ];
        if(isset($user_name) && $user_name != ''){
            $where['s_u_name|s_u_phone'] = ['like', '%' . $user_name . '%'];
        }
        if(isset($user_name1) && $user_name1 != ''){
            $where['s_com_name|s_name|s_phone|s_phone1|s_phone2'] = ['like', '%' . $user_name1 . '%'];
        }
        if(isset($user_name2) && $user_name2 != ''){
            $where['s_inside_name|s_inside_phone'] = ['like', '%' . $user_name2 . '%'];
        }
        if(isset($s_verify) && $s_verify != ''){
            $where['s_verify'] = $s_verify;
        }
        if(isset($s_status) && $s_status != ''){
            $where['s_status'] = $s_status;
            if($s_status == 3){
                $where['s_status'] = ['>=',3];
            }
        }
        if(isset($province) && $province != ''){
            $where['s_sheng'] = $province;
        }
        if(isset($city) && $city != ''){
            $where['s_shi'] = $city;
        }
        if(isset($district) && $district != ''){
            $where['s_qu'] = $district;
        }
        if(isset($address) && $address != ''){
            $where['s_address|s_area_str'] = ['like', '%' . $address . '%'];
        }
        if(isset($s_admin) && $s_admin != ''){
            $where['s_admin'] = $s_admin;
        }
        if(isset($s_protect) && $s_protect != ''){
            $where['s_protect'] = $s_protect;
        }
        if($ids){
            if($ids != 'all'){
                $where['s_id'] = ['in',$ids];
            }
        }else{
            return json(['code'=>0,'msg'=>'请选择数据！']);
        }
        $field = ['s_u_name','s_u_phone','s_com_name','s_name', 's_phone',
            's_phone1','s_phone2','s_verify','s_reason','s_status','s_time','s_endtime',
            's_update_time','s_inside_name','s_inside_phone','s_area_str','s_address',
            's_lon','s_lat','s_days','s_admin','s_year_other','s_time_other','s_done_other','s_protect'
        ];
        $data = Db::table('up_shop')
            ->where($where)
            ->order('s_status asc,s_verify asc,s_update_time desc')
            ->field($field)
            ->select();
        if($data){
            $cellname = [
                '报备人', '报备人电话', '店名', '联系人', '联系电话1',
                '联系电话2', '联系电话3', '审核状态', '拒绝原因(如拒绝)', '加盟状态', '提交时间', '有效截止时间',
                '更新时间', '老师名字', '老师电话', '地址', '地址详情',
                '经度', '纬度', '保护天数', '数据类型', '年任务量','保护时间','是否完成当年任务','是否加入保护区域'
            ];
            $status = ['s_verify','s_status','s_admin'];
            $values = [
                's_verify' => [1 => '待审核',2 => '审核通过',3 => '审核拒绝'],
                's_status' => [1 => '待加盟',2 => '已加盟',3 => '已过更新有效期',4 => '已到期'],
                's_admin' => [1 => '前台报备数据',2 => '后台添加真实数据',3 => '后台添加假数据'],
                's_protect' => [1 => '是',2 => '否']
            ];
            $res = exportExcel('shop',time(),$cellname,$data,$status,$values);
            return json($res);
        }else{
            return json(['code'=>0,'msg'=>'此条件下无数据']);
        }
    }

    /**
     * 导入Excel
     * @return mixed
     */
    public function excelIn(){
//        $fname = '暂无';
//        $url = "https://restapi.amap.com/v3/geocode/geo?output=JSON&key=5cde172b572d560a1946eb0dea85834d&address=".$fname;//高德地图
//        $result=https_request($url);
//        $last = json_decode($result,true)['geocodes'][0];
//        $place = explode (',',$last['location']);
//        dump($last);
//        dump($place);
//        die;
        set_time_limit(0);
        if(request()->file('file')){
            $file = request()->file('file');
            $info  = $file->getInfo();
            $file_path = $info['tmp_name'];
            //取出文件后缀
            $file_type = explode(".",$info['name']);
            $file_type = end($file_type);
            vendor("PHPExcel.PHPExcel.IOFactory");
            if( $file_type == 'xls' ){
                $inputFileType = 'Excel5';    //这个是读 xls的
            }else{
                $inputFileType = 'Excel2007';//这个是计xlsx的
            }
            $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($file_path);
            $objWorksheet = $objPHPExcel->getActiveSheet();
            $highestRow = $objWorksheet->getHighestRow();
//            print_r($highestRow);die;
            $today = date('Y-m-d H:i:s');
            $data = [];
//            $highestRow=100;
            for ($i=2;$i<=$highestRow;$i++) {
                $a = $objPHPExcel->getActiveSheet ()->getCell ("A" . $i)->getValue ();//客户名字
                $b = $objPHPExcel->getActiveSheet ()->getCell ("B" . $i)->getValue ();//客户电话
                $c = $objPHPExcel->getActiveSheet ()->getCell ("C" . $i)->getValue ();//地址
                if($a != '' && $b != '' && $c != ''){
                    $url = "https://restapi.amap.com/v3/geocode/geo?output=JSON&key=5cde172b572d560a1946eb0dea85834d&address=".$c;//高德地图
                    $result=https_request($url);
                    if(isset(json_decode($result,true)['geocodes'][0]) && json_decode($result,true)['geocodes'][0]) {


                        $last = json_decode($result, true)['geocodes'][0];
                        $place = explode(',', $last['location']);
                        if ($last['province']) {
                            $expl = $last['province'];
                            if ($last['province'] == '北京市') {
                                $last['province'] = '北京';
                            }
                            if ($last['province'] == '天津市') {
                                $last['province'] = '天津';
                            }
                            if ($last['province'] == '重庆市') {
                                $last['province'] = '重庆';
                            }
                            if ($last['province'] == '上海市') {
                                $last['province'] = '上海';
                            }
                        } else {
                            $last['province'] = '';
                        }
                        if (isset($last['city']) && $last['city']) {
                            $expl = $last['city'];
                        } else {
                            $last['city'] = '';
                        }
                        if (isset($last['district']) && $last['district']) {
                            $expl = $last['district'];
                        } else {
                            $last['district'] = '';
                        }
                        $add_arr = explode($expl, $c);
                        if (count($add_arr) > 1) {
                            $s_address = $add_arr[1];
                        } else {
                            $s_address = $add_arr[0];
                        }
                        if (!$place) {
                            $place = [
                                '',
                                ''
                            ];
                        }
                        $update = [
                            's_u_userid' => 0,
                            's_com_name' => '特惠商品',
                            's_name' => $a,
                            's_phone' => $b,
                            's_inside_name' => '',
                            's_inside_phone' => '',
                            's_verify' => 2,
                            's_status' => 2,
                            's_time' => $today,
                            's_endtime' => $today,
                            's_update_time' => $today,
                            's_address' => $s_address,
                            's_stree' => '',
                            's_area_str' => $last['province'] . ' ' . $last['city'] . ' ' . $last['district'],
                            's_lon' => $place[0],
                            's_lat' => $place[1],
                            's_foradmin_time' => $today,
                            's_admin' => 2,
                            's_excelin' => 2
                        ];
                        if ($last['province']) {
                            $update['s_sheng'] = Db::table('up_area')
                                ->where('district', $last['province'])->where('level', 1)->value('district_id');
                            if ($update['s_sheng'] && $last['city']) {
                                $update['s_shi'] = Db::table('up_area')
                                    ->where('district', $last['city'])->where('pid', $update['s_sheng'])->where('level', 2)
                                    ->value('district_id');
                                if ($update['s_shi'] && $last['district']) {
                                    $update['s_qu'] = Db::table('up_area')
                                        ->where('district', $last['district'])->where('pid', $update['s_shi'])->where('level', 3)
                                        ->value('district_id');
                                }
                            }
                        }
                        $res = Db::table('up_shop')->insert($update);
                    }else{
                        echo '找不到经纬度地址';
                    }
                }else{
                    echo '表格数据不全';
                }
            }

        }
        return $this->fetch('dyn/excelin');
    }
}